package example.service;

import example.dao.connect;
import org.json.JSONException;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;

public class user_c {

    public static String diqu_get() throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        Statement stmt = conn.createStatement();
        String sql = "select * from quyu";
        ResultSet p = stmt.executeQuery(sql);
        return jsonzhuan.resultSetToJson(p);
    }

    public static String yonghu_get() throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        Statement stmt = conn.createStatement();
        String sql = "select username from login";
        ResultSet p = stmt.executeQuery(sql);
        return jsonzhuan.resultSetToJson(p);
    }

    public static String selfzhuce(String username,String password,String name,String phone,String selfdiqu) {
        try
        {
            Connection conn = connect.getConnection();
            String sql="insert into login ( username , password , shenfen ) values('"+username+"','"+password+"','1')";
            Statement stmt=conn.createStatement();//创建一个Statement对象
            stmt.executeUpdate(sql);//执行sql语句
            String sql1="insert into users ( username , name , address , phone ) values ( '"+username+"','"+name+"','"+selfdiqu+"','"+phone+"' )";
            Statement stmt1=conn.createStatement();//创建一个Statement对象
            stmt1.executeUpdate(sql1);//执行sql语句
            return "1";
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            System.out.println("数据库连接出错");
            return "2";
        }
    }

    public static String sellzhuce(String username,String password,String name,String phone,String[] selfdiqu) {
        String quyu = "";
        try
        {
            Connection conn = connect.getConnection();
            String sql="insert into login ( username , password , shenfen ) values('"+username+"','"+password+"','2')";
            Statement stmt=conn.createStatement();//创建一个Statement对象
            stmt.executeUpdate(sql);//执行sql语句

            Statement stmt2 = conn.createStatement();
            String sql2 = "select * from quyu";
            ResultSet p = stmt2.executeQuery(sql2);
            while ( p.next() ){
                for( int i = 0 ; i < selfdiqu.length ; i++ ){
                    if ( selfdiqu[i].equals(p.getString("diquname"))){
                        String fuzhi = p.getString("diqu");
                        quyu = quyu +","+fuzhi;
                    }
                }
            }
            String sql1="insert into users ( username , name , address , phone ) values ( '"+username+"','"+name+"','"+quyu+"','"+phone+"' )";
            Statement stmt1=conn.createStatement();//创建一个Statement对象
            stmt1.executeUpdate(sql1);//执行sql语句
            return "1";
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            System.out.println("数据库连接出错");
            return "2";
        }
    }


    public static void change(String username , String password , String name , String phone) throws SQLException, JSONException {
        Connection conn = connect.getConnection();
        String sql = "update login set password = '"+password+"' where username = '"+username+"'";
        Statement stmt=conn.createStatement();//创建一个Statement对象
        stmt.executeUpdate(sql);//执行sql语句

        String sql1 = "update users set name = '"+name+"' , phone = '"+phone+"' where username = '"+username+"'";
        Statement stmt1=conn.createStatement();//创建一个Statement对象
        stmt1.executeUpdate(sql1);//执行sql语句
    }

}
